package com.lagou.dao;

import com.lagou.entity.Student;
import com.lagou.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentInfoDao {

    public static Student query(String keyword) {

        String sql = "SELECT id ,stuId,NAME,sex,birthday FROM student WHERE stuId LIKE '%'+?+'%'  OR NAME LIKE '%'+?+'%' LIMIT 1";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        Student student = null;


        try {
            con = JDBCUtils.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, keyword);
            ps.setString(2, keyword);
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                int id = resultSet.getInt("id");
                String stuId = resultSet.getString("stuId");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                String birthday = resultSet.getString("birthday");
                student = new Student(id, stuId, name, sex, birthday);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con, ps, resultSet);
        }

        return student;

    }

    public static List<Student> queryAll() {

        String sql = "SELECT id ,stuId,NAME,sex,birthday FROM student";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        List<Student> studentList = new ArrayList<>();

        try {
            con = JDBCUtils.getConnection();
            ps = con.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String stuId = resultSet.getString("stuId");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                String birthday = resultSet.getString("birthday");
                studentList.add(new Student(id, stuId, name, sex, birthday));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con, ps, resultSet);
        }

        return studentList;

    }

    public static int add(Student stu) {

        String sql = "INSERT INTO student (stuId,NAME,sex,birthday) VALUES(?,?,?,?)";
        Connection con = null;
        PreparedStatement ps = null;

        int flag = 0;

        try {
            con = JDBCUtils.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, stu.getStuId());
            ps.setString(2, stu.getName());
            ps.setString(3, stu.getSex());
            ps.setString(4, stu.getBirthday());
            flag = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con, ps);
        }

        return flag;

    }

    public static int edit(Student stu) {

        String sql = "UPDATE student SET stuId = ?,NAME=?,sex=?,birthday=? WHERE id=?";
        Connection con = null;
        PreparedStatement ps = null;

        int flag = 0;

        try {
            con = JDBCUtils.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, stu.getStuId());
            ps.setString(2, stu.getName());
            ps.setString(3, stu.getSex());
            ps.setString(4, stu.getBirthday());
            ps.setInt(5, stu.getId());
            flag = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con, ps);
        }

        return flag;

    }


    public static int delete(int id) {

        String sql = "DELETE  FROM student WHERE Id = ? ";
        Connection con = null;
        PreparedStatement ps = null;

        int flag = 0;

        try {
            con = JDBCUtils.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            flag = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con, ps);
        }

        return flag;

    }


}
